﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace AwesomeEnginePC.SQL
{
    public abstract class MsSqlConnector : ConnectorBase
    {

        public MsSqlConnector(SqlConnection msSqlConnection)
            : base(msSqlConnection)
        {
        }

        public MsSqlConnector(string hostName, string database, string userName, string password)
            : base(hostName, database, userName, password)
        {
        }

        /// <summary>
        /// Runs a given stored procedure. Will not return a result. Will throw an exception if there is a connection error or execution error.
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameters"></param>
        protected SQLQueryResult Exec(string storedProcedureName, Action<SqlCommand> parameters)
        {
            SqlConnection conn = null;

            try
            {
                conn = MsSqlConnectionString;
                conn.Open();
            }
            catch (Exception ex)
            {
                conn.Close();
                string message = ex.Message;
                return SQLQueryResult.FailedToConnect;
            }

            try
            {
                SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                if (parameters != null)
                {
                    parameters(cmd);
                }

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                conn.Close();
                string message = ex.Message;
                return SQLQueryResult.FailedToExecute;
            }

            conn.Close();
            return SQLQueryResult.Sucessful;
        }


        protected SQLQueryResult ExecUseDataSet(string storedProcedureName, Action<SqlCommand> parameters, Action<DataSet> returnAction)
        {
            SqlConnection conn = null;
            DataSet dataSet = null;

            try
            {
                conn = MsSqlConnectionString;
                conn.Open();
            }
            catch (Exception ex)
            {
                conn.Close();
                string message = ex.Message;
                return SQLQueryResult.FailedToConnect;
            }

            try
            {
                SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                if (parameters != null)
                {
                    parameters(cmd);
                }

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;

                dataSet = new DataSet();
                adapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                conn.Close();
                string message = ex.Message;
                return SQLQueryResult.FailedToExecute;
            }

            try
            {
                if (returnAction != null)
                {
                    returnAction(dataSet);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                string message = ex.Message;
                return SQLQueryResult.FailedToSaveToDataSet;
            }

            conn.Close();
            return SQLQueryResult.Sucessful;
        }

    }
}
